Challenge #17: Month-over-Month Retention Rate ～月ごとの顧客維持率のバッチマクロ～ – Alteryx Weekly Challenge

こんにちは。Alteryxサポートエンジニアとして勉強中のスズです。

Alteryx Communityで公開されているWeekly Challengeに挑戦するシリーズ。今回は月ごとの顧客維持率のバッチマクロの作成に挑戦します。

動作環境

当エントリの執筆には、以下の環境を利用しています。

• Windows 10 Pro
• Alteryx Designer 2018.4.5.55178 英語版

Challenge #17: Month-over-Month Retention Rate

お題

今回挑戦するお題はこちら。

• Challenge #17: Month-over-Month Retention Rate - Alteryx Community

Input側のデータはこちら。

Output側のデータはこちら。

「Start of Month」列の24か月前に開いている口座の数と「Start of Month」列の月に閉じられた口座の数で、銀行の月ごとの顧客維持率を計算します。また、今回のお題はバッチマクロを作成して回答します。

通常のワークフローを作成

バッチマクロの作成の前に、まずは通常のワークフローを作成して、Output側と同じになるデータを作成します。

Text Inputツール（In/Out -> Text Input）を使用して、2つのデータを準備しました。1つ目はInput側のデータの日付形式を修正したものです。

2つ目は「Start of Month」列のデータです。

Append Fieldツール（Join -> Append Field）を使用して2つのデータを結合し、Filterツール（Preparation -> Filter）を使用して必要なデータを抽出します。Filterツールの条件としては以下の通り設定しています。

[Open Date] > DateTimeAdd([Start of Month],-2,'years') and [Open Date] < [Start of Month] and ([Close Date] > [Start of Month] or IsNull([Close Date]))

• 「Open Date」列が「Start of Month」列の2年前より後
• 「Open Date」列が「Start of Month」列より前
• 「Close Date」列が「Start of Month」列より後またはNullではない

抽出したデータから、開いている口座と閉じた口座の数を数えます。開いている口座はSummarizeツール（Transform -> Summarize）を使用して、「Start of Month」列をグループ化して数を出力します。

開いている口座のデータは以下のようになっています。

閉じた口座は、Filterツールを使用して「Close Date」列と「Start of Month」列が同じ月であるデータに絞り込んだのち、Summarizeツールで「Start of Month」列をグループ化して数を出力します。

Filterツールには以下の式を設定しています。

Left([Close Date], 7) = Left([Start of Month], 7)

閉じた口座のデータは以下のようになっています。

開いている口座と閉じた口座のデータを、Joinツール（Join -> Join）を使用して「Start of Month」列で結合します。また、Joinツールで結合したデータと、開いている口座のうち結合できなかったデータを、 Unionツール（Join -> Union）を使用して結合します。現在使用しているデータはJoinツールで全て結合されているためUnionツールの処理は不要ですが、別のデータで結合できなかったデータがあった場合を想定してツールを追加しています。

Formulaツール（Preparation -> Formula）を使用して「%」列をDouble型で作成します。計算式はOutput側のデータにあわせて設定しています。

[Close] / [Open]

Selectツール（Preparation -> Select）を使用して列の並べ替えを行い、Data Cleansingツール（Preparation -> Data Cleansing）を使用してNullを0に変換します。現在使用しているデータではNullがないためData Cleansingツールの処理は不要ですが、別のデータでNullがあった場合を想定してツールを追加しています。

Output側のデータと同じデータが出力されていることを確認します。

バッチマクロの作成

作成したワークフローからバッチマクロを作成します。

1つ目のデータを設定したText Inputツールの右クリックメニューを開き、Convert To Macro Input でMacro Inputツール（Interface -> Macro Input）に変換します。Macro Inputツールの設定として、Text InputのEdit Dataを開き、以下のように1行だけデータを残します。

2つ目のデータを設定したText Inputツールは、以下のように1行だけデータを残します。

Controle Parameterツール（Interface -> Control Parameter）を追加し、2つ目のデータを設定したText Inputツールに接続します。ツールの間にはActionツール（Interface -> Action）が自動的に配置されます。Actionツールは、Select an action type に Update Cell (Default) を選択し、 Update row 1 and column 1 (2013-05-01) to .... と設定します。

Data Cleansingツールの先にはBrowseツール（In/Out -> Browse）の代わりにMacro Outputツール（Interface -> Macro Output）を接続します。

ワークフローの設定ウィンドウにあるWorkflowタブを確認すると、TypeのMacroにBatch Macroが自動的に設定されています。

View -> Interface Designer を開き、バッチマクロを使用する際の設定を確認します。レイアウトは以下のようになっています。

またバッチマクロ用のアイコンを設定しました。

バッチマクロを使用する

バッチマクロを使用してワークフローを作成します。

Input側に用意されているデータは、「Open Date」列と「Close Date」列が文字列型になっています。Formulaツールを使用してDate型に変換します。

「Open Date」列は、新しく「New_Open Date」列を作成し、以下の式で日付形式に変換します。「Close Date」列に関しても同様の処理を行います。

DateTimeParse([Open Date],"%B%d,%Y")

Selectツールを利用して、不要な列の削除と列名の修正を行います。

また、Text Inputツールを使用して、「Start of Month」列のデータを用意します。

作成したバッチマクロを追加します。バッチマクロの上の入力には「Start of Month」列のデータを接続し、下の入力には「Open Date」と「Close Date」があるデータを接続します。バッチマクロの設定には、入力したデータにあわせて列を設定します。

結果を確認して完成です。

最後に

今回はWeekly ChallengeのChallenge #17: Month-over-Month Retention Rateに挑戦しました。

サンプルワークフローのご案内

クラスメソッドでご契約のユーザー様向けに、当ブログでご紹介したワークフローを専用ポータルにて公開しております。当エントリに関しては、バッチマクロを使用するでご紹介したワークフローを公開しておりますが、ワークフローにはバッチマクロも付属しておりますので、マクロの中身もご確認いただけます。ご参考にしていただければ幸いです。